﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using InformationSystem.Framework.Dao;
using InformationSystem.BLL.Core.Model;
using Spring.Data.Common;
using System.Data;
using Spring.Data.Generic;

namespace InformationSystem.BLL.Core.Dao.Impl
{
    public class InformationDaoImpl : InfoSysAdoDaoSupport, IInformationDao
    {
        public bool SendMail(Mail mail)
        {
            string insertmainSql = @"insert into 
                                    Mail 
                                 (mailid,mailsubject,senduserid,sendusername,mailstatus,hasattachment,mailto,content,createtime,sendtime)
                                  values (@mailid,@mailsubject,@senduserid,@sendusername,@mailstatus,@hasattachment,@mailto,@content,@createtime,@sendtime)";
            IDbParametersBuilder builder = null;

            int useableid = base.GetUseableID("Mail", "mailid");
            builder = CreateDbParametersBuilder();
            builder.Create().Name("mailid").Type(DbType.String).Value(useableid);
            builder.Create().Name("mailsubject").Type(DbType.String).Value(mail.MailSubject);
            builder.Create().Name("senduserid").Type(DbType.String).Value(mail.SendUserID);
            builder.Create().Name("sendusername").Type(DbType.String).Value(mail.SendUserName);
            builder.Create().Name("mailstatus").Type(DbType.Int32).Value(mail.MailStatus);
            builder.Create().Name("hasattachment").Type(DbType.Boolean).Value(mail.HasAttachment);
            builder.Create().Name("mailto").Type(DbType.String).Value(mail.RecvString);
            builder.Create().Name("content").Type(DbType.String).Value(mail.Content);
            builder.Create().Name("createtime").Type(DbType.DateTime).Value(mail.CreateTime);
            builder.Create().Name("sendtime").Type(DbType.DateTime).Value(mail.SendTime);

            AdoTemplate.ExecuteNonQuery(CommandType.Text, insertmainSql, builder.GetParameters());
            mail.MailID = useableid;


            //保存接收者
            string insertGroupSql = "insert into MailRecvGroup (mailid,collegeid) values (@mailid,@collegeid)";
            foreach (var item in mail.RecvGroupList)
            {
                builder = CreateDbParametersBuilder();
                builder.Create().Name("mailid").Type(DbType.String).Value(mail.MailID);
                builder.Create().Name("collegeid").Type(DbType.String).Value(item.CollegeID);
                AdoTemplate.ExecuteNonQuery(CommandType.Text, insertGroupSql, builder.GetParameters());
            }

            string insertUserSql = "insert into MailRecvUser (mailid,recvuserid,recvusername) values (@mailid,@recvuserid,@recvusername)";
            foreach (var item in mail.RecvUserList)
            {
                builder = CreateDbParametersBuilder();
                builder.Create().Name("mailid").Type(DbType.String).Value(mail.MailID);
                builder.Create().Name("recvuserid").Type(DbType.String).Value(item.UserID);
                builder.Create().Name("recvusername").Type(DbType.String).Value(item.UserName);
                AdoTemplate.ExecuteNonQuery(CommandType.Text, insertUserSql, builder.GetParameters());
            }
            //保存附件信息
            string insertAttachmentSql = "insert into MailAttachment (mailid,savedfilename,uploadfilename) values (@mailid,@savedfilename,@uploadfilename)";
            if (mail.AttachmentList != null)
            {
                foreach (var item in mail.AttachmentList)
                {
                    builder = CreateDbParametersBuilder();
                    builder.Create().Name("mailid").Type(DbType.String).Value(mail.MailID);
                    builder.Create().Name("savedfilename").Type(DbType.String).Value(item.SavedFileName);
                    builder.Create().Name("uploadfilename").Type(DbType.String).Value(item.UploadFileName);
                    AdoTemplate.ExecuteNonQuery(CommandType.Text, insertAttachmentSql, builder.GetParameters());
                }
            }
            return true;
        }

        public bool SaveMail(Mail mail)
        {
            throw new NotImplementedException();
        }

        public IList<Mail> GetMailSendList(string sendUserID)
        {
            string selectSql = @"select
                                    mailid,mailsubject,senduserid,sendusername,mailstatus,hasattachment,mailto,createtime,sendtime,'' as content 
                                from 
                                    Mail
                                where senduserid=@senduserid and mailstatus=@mailstatus order by sendtime desc";

            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("senduserid").Type(DbType.String).Value(sendUserID);
            builder.Create().Name("mailstatus").Type(DbType.Int32).Value(MailStatus.Sent);
            //builder.Create().Name("mailstatus").Type(DbType.Int32).Value("2");
            return AdoTemplate.QueryWithRowMapper<Mail>(CommandType.Text, selectSql, new MailRow<Mail>(), builder.GetParameters());
        }

        public IList<Mail> GetMailRecvList(string recvuserID)
        {
            string selectSql = @"select
                                    m.mailid,m.mailsubject,m.senduserid,m.sendusername,m.mailstatus,m.hasattachment,m.mailto,m.createtime,m.sendtime,'' as content 
                                from 
                                    Mail As m , MailRecvUser As mr
                                where m.mailid=mr.mailid and mr.recvuserid=@recvuserid order by sendtime desc";

            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("recvuserid").Type(DbType.String).Value(recvuserID);
            builder.Create().Name("mailstatus").Type(DbType.Int32).Value(MailStatus.Sent);
            return AdoTemplate.QueryWithRowMapper<Mail>(CommandType.Text, selectSql, new MailRow<Mail>(), builder.GetParameters());
        }

        public IList<Mail> GetMailDraftList(string senduserID)
        {
            throw new NotImplementedException();
        }

        internal class MailRow<T> : IRowMapper<T> where T : Mail, new()
        {
            public T MapRow(IDataReader reader, int rowNum)
            {
                T mail = new T();
                mail.MailID = reader.GetInt32(reader.GetOrdinal("mailid"));
                mail.MailSubject = reader.GetString(reader.GetOrdinal("mailsubject"));
                mail.SendUserID = reader.GetString(reader.GetOrdinal("senduserid"));
                mail.SendUserName = reader.GetString(reader.GetOrdinal("sendusername"));
                mail.MailStatus = (MailStatus)reader.GetInt32(reader.GetOrdinal("mailstatus"));
                mail.HasAttachment = reader.GetBoolean(reader.GetOrdinal("hasattachment"));
                mail.RecvString = reader.GetString(reader.GetOrdinal("mailto"));
                mail.Content = reader.GetString(reader.GetOrdinal("content"));
                mail.CreateTime = reader.GetDateTime(reader.GetOrdinal("createtime"));
                mail.SendTime = reader.GetDateTime(reader.GetOrdinal("sendtime"));
                return mail;
            }
        }

        public bool DeleteMailSent(int mailID, string senduserid)
        {
            string deleteSql = @"delete from Mail where autoid=@autoid and senduserid=@senduserid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("autoid").Type(DbType.Int32).Value(mailID);
            builder.Create().Name("senduserid").Type(DbType.String).Value(senduserid);
            return AdoTemplate.ExecuteNonQuery(CommandType.Text, deleteSql, builder.GetParameters()) == 1;
        }

        public bool DeleteMailAttachment(int mailID)
        {
            string deleteSql = @"delete from MailAttachment where mailid=@mailid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("mailid").Type(DbType.Int32).Value(mailID);
            return AdoTemplate.ExecuteNonQuery(CommandType.Text, deleteSql, builder.GetParameters()) == 1;
        }

        public bool UpdateMailStatus(int mailID, MailStatus mailstatus)
        {
            string updateSql = "update Mail set mailstatus=@mailstatus where mailid=@mailid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("mailstatus").Type(DbType.Int32).Value(mailstatus);
            builder.Create().Name("mailid").Type(DbType.Int32).Value(mailID);
            return AdoTemplate.ExecuteNonQuery(CommandType.Text, updateSql, builder.GetParameters()) == 1;
        }

        public bool DeleteMailRecvd(int mailID, string recvuserid)
        {
            string deleteSql = @"delete from MailRecvUser where mailid=@mailid and recvuserid=@recvuserid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("mailid").Type(DbType.Int32).Value(mailID);
            builder.Create().Name("recvuserid").Type(DbType.String).Value(recvuserid);
            return AdoTemplate.ExecuteNonQuery(CommandType.Text, deleteSql, builder.GetParameters()) == 1;
        }

        public Mail GetMailDetailByID(int mailID)
        {
            string selectSql = @"select
                                    mailid,mailsubject,senduserid,sendusername,mailstatus,hasattachment,mailto,content,createtime,sendtime
                                from 
                                    Mail
                                where mailid=@mailid";

            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("mailid").Type(DbType.Int32).Value(mailID);
            try
            {
                return AdoTemplate.QueryForObject<Mail>(CommandType.Text, selectSql, new MailRow<Mail>(), builder.GetParameters());
            }
            catch (Exception)
            {
                return null;
            }
        }


        public IList<RecvUser> GetMailRecvUserList(int mailID)
        {
            string selectSql = @"select recvuserid,
	                                    case when sy.username is null then 
	                                    (case when stu.username is null then N'未知用户'  else stu.username end) else  
	                                    sy.username	 end as username
                                    from
                                    MailRecvUser as main left join SystemUser as sy on main.recvuserid=sy.userid
                                    left join StudentUser as stu on main.recvuserid=stu.userid
                                    where main.mailid=@mailid";

            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("mailid").Type(DbType.String).Value(mailID);
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());
            IList<RecvUser> userlist = new List<RecvUser>();
            foreach (DataRow item in dt.Rows)
            {
                RecvUser user = new RecvUser();
                user.UserID = item["recvuserid"].ToString();
                user.UserName = item["username"].ToString();
                userlist.Add(user);
            }
            return userlist;
        }

        public IList<Attachment> GetMailAttachments(int mailID)
        {
            string selectSql = @"select [autoid],savedfilename,uploadfilename
                                    from
                                    MailAttachment
                                    where mailid=@mailid";


            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("mailid").Type(DbType.Int32).Value(mailID);
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());
            IList<Attachment> attachmentlist = new List<Attachment>();
            foreach (DataRow item in dt.Rows)
            {
                Attachment attachment = new Attachment();
                attachment.FileID = (int)item["autoid"];
                attachment.RefParentID = mailID;
                attachment.UploadFileName = item["uploadfilename"].ToString();
                attachment.SavedFileName = item["savedfilename"].ToString();
                attachmentlist.Add(attachment);
            }
            return attachmentlist;
        }

        public Attachment GetMailAttachment(int mailid, int fileid)
        {
            string selectSql = @"select 
	                                savedfilename,uploadfilename 
                                from 
	                                MailAttachment
	                                inner join Mail on MailAttachment.mailid=Mail.mailid
                                where
	                                MailAttachment.mailid=@mailid and autoid=@autoid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("mailid").Type(DbType.Int32).Value(mailid);
            builder.Create().Name("autoid").Type(DbType.Int32).Value(fileid);
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());
            if (dt.Rows.Count == 0) return null;
            Attachment att = new Attachment();
            att.FileID = fileid;
            att.RefParentID = mailid;
            att.SavedFileName = dt.Rows[0]["savedfilename"].ToString();
            att.UploadFileName = dt.Rows[0]["uploadfilename"].ToString();
            return att;
        }


        public bool SendAffiche(Affiche affiche)
        {

            string insertmainSql = @"insert into 
                                    AfficheInfo 
                                 (afficheinfoid,title,keyword,commituserid,commitusername,collegeid,gradeid,ispublictocollege,hasattachment,sendtime,content)
                                  values (@afficheinfoid,@title,@keyword,@commituserid,@commitusername,@collegeid,@gradeid,@ispublictocollege,@hasattachment,@sendtime,@content)";
            IDbParametersBuilder builder = null;

            int useableid = base.GetUseableID("AfficheInfo", "afficheinfoid");
            builder = CreateDbParametersBuilder();
            builder.Create().Name("afficheinfoid").Type(DbType.Int32).Value(useableid);
            builder.Create().Name("title").Type(DbType.String).Value(affiche.Title);
            builder.Create().Name("keyword").Type(DbType.String).Value(affiche.KeyWord);
            builder.Create().Name("commituserid").Type(DbType.String).Value(affiche.CommitUserID);
            builder.Create().Name("commitusername").Type(DbType.String).Value(affiche.CommitUserName);
            builder.Create().Name("collegeid").Type(DbType.Int32).Value(affiche.CollegeID);
            builder.Create().Name("gradeid").Type(DbType.Int32).Value(affiche.GradeID);
            builder.Create().Name("ispublictocollege").Type(DbType.Boolean).Value(affiche.IsPublicToCollege);
            builder.Create().Name("hasattachment").Type(DbType.Boolean).Value(affiche.HasAttachment);
            builder.Create().Name("sendtime").Type(DbType.DateTime).Value(affiche.SendTime);
            builder.Create().Name("content").Type(DbType.String).Value(affiche.Content);

            AdoTemplate.ExecuteNonQuery(CommandType.Text, insertmainSql, builder.GetParameters());
            affiche.AfficheID = useableid;

            //保存附件信息

            string insertAttachmentSql = "insert into AfficheAttachment (afficheid,savedfilename,uploadfilename) values (@afficheid,@savedfilename,@uploadfilename)";
            if (affiche.AttachmentList != null)
            {
                foreach (var item in affiche.AttachmentList)
                {
                    builder = CreateDbParametersBuilder();
                    builder.Create().Name("afficheid").Type(DbType.Int32).Value(affiche.AfficheID);
                    builder.Create().Name("savedfilename").Type(DbType.String).Value(item.SavedFileName);
                    builder.Create().Name("uploadfilename").Type(DbType.String).Value(item.UploadFileName);
                    AdoTemplate.ExecuteNonQuery(CommandType.Text, insertAttachmentSql, builder.GetParameters());
                }
            }
            return true;
        }

        public bool DeleteAfficheByID(int afficheID)
        {
            string deleteattachment = "delete from AfficheAttachment where afficheid=@afficheinfoid";
            string deletereadlist = "delete from AfficheReadList where afficheid=@afficheinfoid";
            string deleteSql = @"delete from AfficheInfo where afficheinfoid=@afficheinfoid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("afficheinfoid").Type(DbType.Int32).Value(afficheID);
            AdoTemplate.ExecuteNonQuery(CommandType.Text, deleteattachment, builder.GetParameters());
            AdoTemplate.ExecuteNonQuery(CommandType.Text, deletereadlist, builder.GetParameters());
            AdoTemplate.ExecuteNonQuery(CommandType.Text, deleteSql, builder.GetParameters());
            return true;
        }

        public IList<Affiche> GetSentAfficheList(LoginUser user)
        {
            string selectSql = @"select 
                                    afficheinfoid,title,keyword,commituserid,commitusername,collegeid,gradeid,ispublictocollege,hasattachment,sendtime, '' as [content],Cast(0 as bit) as hasread
                                from AfficheInfo
                                 where commituserid=@commituserid
                                order by sendtime desc";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("commituserid").Type(DbType.String).Value(user.UserID);
            return AdoTemplate.QueryWithRowMapper<Affiche>(CommandType.Text, selectSql, new AfficheRow<Affiche>(), builder.GetParameters());
        }

        public IList<Affiche> GetRecvAfficheList(LoginUser user, int recordcount)
        {
            string selectSql = @"select {0}
                                    afficheinfoid,title,keyword,commituserid,commitusername,collegeid,gradeid,ispublictocollege,hasattachment,sendtime, '' as [content],
                                    case when people.readuserid is null then Cast(0 as bit) else Cast(1 as bit) end as hasread
                                from AfficheInfo left join (select afficheid,readuserid from AfficheReadList where readuserid=@readuserid) as people on afficheinfoid=people.afficheid
                                 where (collegeid=@collegeid and gradeid=@gradeid) or (collegeid=@collegeid and ispublictocollege=1) or (collegeid=0 and gradeid=0)
                                order by sendtime desc";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("collegeid").Type(DbType.Int32).Value(user.CollegeID);
            builder.Create().Name("gradeid").Type(DbType.Int32).Value(user.GradeID);
            builder.Create().Name("readuserid").Type(DbType.String).Value(user.UserID);
            return AdoTemplate.QueryWithRowMapper<Affiche>(CommandType.Text, string.Format(selectSql, recordcount == 0 ? "" : ("top " + recordcount.ToString())), new AfficheRow<Affiche>(), builder.GetParameters());
        }

        internal class AfficheRow<T> : IRowMapper<T> where T : Affiche, new()
        {
            public T MapRow(IDataReader reader, int rowNum)
            {
                T affiche = new T();
                affiche.AfficheID = reader.GetInt32(reader.GetOrdinal("afficheinfoid"));
                affiche.Title = reader.GetString(reader.GetOrdinal("title"));
                affiche.KeyWord = reader.GetString(reader.GetOrdinal("keyword"));
                affiche.CommitUserID = reader.GetString(reader.GetOrdinal("commituserid"));
                affiche.CommitUserName = reader.GetString(reader.GetOrdinal("commitusername"));
                affiche.CollegeID = reader.GetInt32(reader.GetOrdinal("collegeid"));
                affiche.GradeID = reader.GetInt32(reader.GetOrdinal("gradeid"));
                affiche.IsPublicToCollege = reader.GetBoolean(reader.GetOrdinal("ispublictocollege"));
                affiche.HasAttachment = reader.GetBoolean(reader.GetOrdinal("hasattachment"));
                affiche.SendTime = reader.GetDateTime(reader.GetOrdinal("sendtime"));
                affiche.HasRead = reader.GetBoolean(reader.GetOrdinal("hasread"));
                affiche.Content = reader.GetString(reader.GetOrdinal("content"));
                return affiche;
            }
        }

        internal class WorkInfoRow<T> : IRowMapper<T> where T : WorkInfo, new()
        {
            public T MapRow(IDataReader reader, int rowNum)
            {
                T work = new T();
                work.WorkInfoID = reader.GetInt32(reader.GetOrdinal("workinfoid"));
                work.Title = reader.GetString(reader.GetOrdinal("title"));
                work.KeyWord = reader.GetString(reader.GetOrdinal("keyword"));
                work.CommitUserID = reader.GetString(reader.GetOrdinal("commituserid"));
                work.CommitUserName = reader.GetString(reader.GetOrdinal("commitusername"));
                work.CollegeID = reader.GetInt32(reader.GetOrdinal("collegeid"));
                work.GradeID = reader.GetInt32(reader.GetOrdinal("gradeid"));
                work.IsPublicToCollege = reader.GetBoolean(reader.GetOrdinal("ispublictocollege"));
                work.HasAttachment = reader.GetBoolean(reader.GetOrdinal("hasattachment"));
                work.SendTime = reader.GetDateTime(reader.GetOrdinal("sendtime"));
                work.Content = reader.GetString(reader.GetOrdinal("content"));
                work.HasRead = reader.GetBoolean(reader.GetOrdinal("hasread"));
                return work;
            }
        }


        public IList<RecvUser> GetAfficheReadList(int afficheid)
        {
            string selectSql = @"select readuserid,recvusername,readtime
                                    from
                                    AfficheReadList
                                    where afficheid=@afficheid";

            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("afficheid").Type(DbType.Int32).Value(afficheid);
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());
            IList<RecvUser> userlist = new List<RecvUser>();
            foreach (DataRow item in dt.Rows)
            {
                RecvUser user = new RecvUser();
                user.UserID = item["readuserid"].ToString();
                user.UserName = item["recvusername"].ToString();
                user.ReadTime = (DateTime)item["readtime"];
                userlist.Add(user);
            }
            return userlist;
        }

        public Affiche GetAffiche(int afficheid)
        {
            string selectSql = @"select 
                                    afficheinfoid,title,keyword,commituserid,commitusername,collegeid,gradeid,ispublictocollege,hasattachment,sendtime,content,Cast(0 as bit) as hasread
                                from AfficheInfo
                                 where afficheinfoid=@afficheid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("afficheid").Type(DbType.Int32).Value(afficheid);
            try
            {
                return AdoTemplate.QueryForObject<Affiche>(CommandType.Text, selectSql, new AfficheRow<Affiche>(), builder.GetParameters());
            }
            catch (Exception)
            {
                return null;
            }
        }


        public IList<Attachment> GetAfficheAttachments(int afficheid)
        {
            string selectSql = @"select [autoid],savedfilename,uploadfilename
                                    from
                                    AfficheAttachment
                                    where afficheid=@afficheid";


            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("afficheid").Type(DbType.Int32).Value(afficheid);
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());
            IList<Attachment> attachmentlist = new List<Attachment>();
            foreach (DataRow item in dt.Rows)
            {
                Attachment attachment = new Attachment();
                attachment.FileID = (int)item["autoid"];
                attachment.RefParentID = afficheid;
                attachment.UploadFileName = item["uploadfilename"].ToString();
                attachment.SavedFileName = item["savedfilename"].ToString();
                attachmentlist.Add(attachment);
            }
            return attachmentlist;
        }


        public Attachment GetAfficheAttachment(int afficheid, int fileid)
        {
            string selectSql = @"select [autoid],afficheid,savedfilename,uploadfilename
                                    from
                                    AfficheAttachment
                                    where afficheid=@afficheid and [autoid]=@fileid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("afficheid").Type(DbType.Int32).Value(afficheid);
            builder.Create().Name("fileid").Type(DbType.Int32).Value(fileid);
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());
            if (dt.Rows.Count == 0) return null;
            Attachment att = new Attachment();
            att.FileID = fileid;
            att.RefParentID = afficheid;
            att.SavedFileName = dt.Rows[0]["savedfilename"].ToString();
            att.UploadFileName = dt.Rows[0]["uploadfilename"].ToString();
            return att;
        }


        public bool SendWorkInfo(WorkInfo workinfo)
        {
            string insertmainSql = @"insert into WorkInfo
                                        (workinfoid,title,keyword,commituserid,commitusername,collegeid,gradeid,ispublictocollege,hasattachment,sendtime,content)
                                    values (@workinfoid,@title,@keyword,@commituserid,@commitusername,@collegeid,@gradeid,@ispublictocollege,@hasattachment,@sendtime,@content)";
            int useableid = base.GetUseableID("WorkInfo", "workinfoid");
            IDbParametersBuilder builder = null;
            builder = CreateDbParametersBuilder();
            builder.Create().Name("workinfoid").Type(DbType.Int32).Value(useableid);
            builder.Create().Name("title").Type(DbType.String).Value(workinfo.Title);
            builder.Create().Name("keyword").Type(DbType.String).Value(workinfo.KeyWord);
            builder.Create().Name("commituserid").Type(DbType.String).Value(workinfo.CommitUserID);
            builder.Create().Name("commitusername").Type(DbType.String).Value(workinfo.CommitUserName);
            builder.Create().Name("collegeid").Type(DbType.Int32).Value(workinfo.CollegeID);
            builder.Create().Name("gradeid").Type(DbType.Int32).Value(workinfo.GradeID);
            builder.Create().Name("ispublictocollege").Type(DbType.Boolean).Value(workinfo.IsPublicToCollege);
            builder.Create().Name("hasattachment").Type(DbType.Boolean).Value(workinfo.HasAttachment);
            builder.Create().Name("sendtime").Type(DbType.DateTime).Value(workinfo.SendTime);
            builder.Create().Name("content").Type(DbType.String).Value(workinfo.Content);

            AdoTemplate.ExecuteNonQuery(CommandType.Text, insertmainSql, builder.GetParameters());
            workinfo.WorkInfoID = useableid;

            //保存附件信息

            string insertAttachmentSql = "insert into WorkAttachment (workid,savedfilename,uploadfilename) values (@workid,@savedfilename,@uploadfilename)";
            if (workinfo.AttachmentList != null)
            {
                foreach (var item in workinfo.AttachmentList)
                {
                    builder = CreateDbParametersBuilder();
                    builder.Create().Name("workid").Type(DbType.Int32).Value(workinfo.WorkInfoID);
                    builder.Create().Name("savedfilename").Type(DbType.String).Value(item.SavedFileName);
                    builder.Create().Name("uploadfilename").Type(DbType.String).Value(item.UploadFileName);
                    AdoTemplate.ExecuteNonQuery(CommandType.Text, insertAttachmentSql, builder.GetParameters());
                }
            }
            return true;
        }

        public bool DeleteWorkInfoByID(int workInfoid)
        {
            string deleteattachment = "delete from WorkAttachment where workid=@workid";
            string deletereadlist = "delete from WorkReadList where workid=@workid";
            string deleteSql = @"delete from WorkInfo where workinfoid=@workid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("workid").Type(DbType.Int32).Value(workInfoid);
            AdoTemplate.ExecuteNonQuery(CommandType.Text, deleteattachment, builder.GetParameters());
            AdoTemplate.ExecuteNonQuery(CommandType.Text, deletereadlist, builder.GetParameters());
            AdoTemplate.ExecuteNonQuery(CommandType.Text, deleteSql, builder.GetParameters());
            return true;
        }

        public IList<WorkInfo> GetRecvWorkInfoList(LoginUser user, int recordcount)
        {
            string selectSql = @"select workinfoid,title,keyword,commituserid,commitusername,collegeid,gradeid,ispublictocollege,hasattachment,sendtime,'' as content,
                                 case when people.readuserid is null then Cast(0 as bit) else Cast(1 as bit) end as hasread
                                 from WorkInfo  left join (select workid,readuserid from WorkReadList where readuserid=@readuserid) as people on workinfoid=people.workid
                                   where (collegeid=@collegeid and gradeid=@gradeid) or (collegeid=@collegeid and ispublictocollege=1) or (collegeid=0 and gradeid=0)  order by sendtime desc";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("collegeid").Type(DbType.Int32).Value(user.CollegeID);
            builder.Create().Name("gradeid").Type(DbType.Int32).Value(user.GradeID);
            builder.Create().Name("readuserid").Type(DbType.String).Value(user.UserID);
            return AdoTemplate.QueryWithRowMapper<WorkInfo>(CommandType.Text, string.Format(selectSql, recordcount == 0 ? "" : ("top " + recordcount.ToString())), new WorkInfoRow<WorkInfo>(), builder.GetParameters());
        }

        public IList<WorkInfo> GetSentWorkInfoList(LoginUser user)
        {
            string selectSql = @"select workinfoid,title,keyword,commituserid,commitusername,collegeid,gradeid,ispublictocollege,hasattachment,sendtime,'' as content,Cast(0 as bit) as hasread
                                     from WorkInfo where commituserid=@userid order by sendtime desc";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("userid").Type(DbType.String).Value(user.UserID);
            return AdoTemplate.QueryWithRowMapper<WorkInfo>(CommandType.Text, selectSql, new WorkInfoRow<WorkInfo>(), builder.GetParameters());
        }

        public WorkInfo GetWorkInfo(int workInfoid)
        {
            string selectSql = @"select workinfoid,title,keyword,commituserid,commitusername,collegeid,gradeid,ispublictocollege,hasattachment,sendtime, content,Cast(0 as bit) as hasread
                                 from WorkInfo where workinfoid=@workInfoid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("workInfoid").Type(DbType.Int32).Value(workInfoid);
            try
            {
                return AdoTemplate.QueryForObject<WorkInfo>(CommandType.Text, selectSql, new WorkInfoRow<WorkInfo>(), builder.GetParameters());
            }
            catch (Exception)
            {
                return null;
            }
        }

        public IList<RecvUser> GetWorkInfoReadList(int workinfoid)
        {
            string selectSql = @"select readuserid,recvusername,readtime
                                    from
                                    WorkReadList
                                    where workid=@workid";

            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("workid").Type(DbType.Int32).Value(workinfoid);
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());
            IList<RecvUser> userlist = new List<RecvUser>();
            foreach (DataRow item in dt.Rows)
            {
                RecvUser user = new RecvUser();
                user.UserID = item["readuserid"].ToString();
                user.UserName = item["recvusername"].ToString();
                user.ReadTime = (DateTime)item["readtime"];
                userlist.Add(user);
            }
            return userlist;
        }

        public IList<Attachment> GetWorkInfoAttachments(int workInfoid)
        {
            string selectSql = @"select [autoid],savedfilename,uploadfilename
                                    from
                                    WorkAttachment
                                    where workid=@workid";


            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("workid").Type(DbType.Int32).Value(workInfoid);
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());
            IList<Attachment> attachmentlist = new List<Attachment>();
            foreach (DataRow item in dt.Rows)
            {
                Attachment attachment = new Attachment();
                attachment.FileID = (int)item["autoid"];
                attachment.RefParentID = workInfoid;
                attachment.UploadFileName = item["uploadfilename"].ToString();
                attachment.SavedFileName = item["savedfilename"].ToString();
                attachmentlist.Add(attachment);
            }
            return attachmentlist;
        }

        public Attachment GetWorkInfoAttachment(int workInfoid, int fileid)
        {
            string selectSql = @"select [autoid],workid,savedfilename,uploadfilename
                                    from
                                    WorkAttachment
                                    where workid=@workid and [autoid]=@fileid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("workid").Type(DbType.Int32).Value(workInfoid);
            builder.Create().Name("fileid").Type(DbType.Int32).Value(fileid);
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());
            if (dt.Rows.Count == 0) return null;
            Attachment att = new Attachment();
            att.FileID = fileid;
            att.RefParentID = workInfoid;
            att.SavedFileName = dt.Rows[0]["savedfilename"].ToString();
            att.UploadFileName = dt.Rows[0]["uploadfilename"].ToString();
            return att;
        }


        public bool SendMsg(Msg msg)
        {
            string insertmainSql = @"insert into 
                                    Msg 
                                            (messageid,messagesubject,messagekeyword,senduserid,sendusername,hasattachment,sendtime,sendto,content)
                                  values (@messageid,@messagesubject,@messagekeyword,@senduserid,@sendusername,@hasattachment,@sendtime,@sendto,@content)";
            IDbParametersBuilder builder = null;

            int useableid = base.GetUseableID("Msg", "messageid");
            builder = CreateDbParametersBuilder();
            builder.Create().Name("messageid").Type(DbType.Int32).Value(useableid);
            builder.Create().Name("messagesubject").Type(DbType.String).Value(msg.Subject);
            builder.Create().Name("messagekeyword").Type(DbType.String).Value(msg.KeyWord);
            builder.Create().Name("senduserid").Type(DbType.String).Value(msg.SendUserID);
            builder.Create().Name("sendusername").Type(DbType.String).Value(msg.SendUserName);
            builder.Create().Name("hasattachment").Type(DbType.Boolean).Value(msg.HasAttachment);
            builder.Create().Name("sendtime").Type(DbType.DateTime).Value(msg.SendTime);
            builder.Create().Name("sendto").Type(DbType.String).Value(msg.RecvString);
            builder.Create().Name("content").Type(DbType.String).Value(msg.Content);


            AdoTemplate.ExecuteNonQuery(CommandType.Text, insertmainSql, builder.GetParameters());
            msg.MsgID = useableid;

            //保存接收者
            //string insertGroupSql = "insert into MailRecvGroup (mailid,collegeid) values (@mailid,@collegeid)";
            //foreach (var item in mail.RecvGroupList)
            //{
            //    builder = CreateDbParametersBuilder();
            //    builder.Create().Name("mailid").Type(DbType.String).Value(mail.MailID);
            //    builder.Create().Name("collegeid").Type(DbType.String).Value(item.CollegeID);
            //    AdoTemplate.ExecuteNonQuery(CommandType.Text, insertGroupSql, builder.GetParameters());
            //}

            string insertUserSql = "insert into MessageRecvUser (messageid,recvuserid,recvusername) values (@messageid,@recvuserid,@recvusername)";
            foreach (var item in msg.RecvUserList)
            {
                builder = CreateDbParametersBuilder();
                builder.Create().Name("messageid").Type(DbType.String).Value(msg.MsgID);
                builder.Create().Name("recvuserid").Type(DbType.String).Value(item.UserID);
                builder.Create().Name("recvusername").Type(DbType.String).Value(item.UserName);
                AdoTemplate.ExecuteNonQuery(CommandType.Text, insertUserSql, builder.GetParameters());
            }
            //保存附件信息
            string insertAttachmentSql = "insert into MessageAttachment (messageid,savedfilename,uploadfilename) values (@messageid,@savedfilename,@uploadfilename)";
            if (msg.AttachmentList != null)
            {
                foreach (var item in msg.AttachmentList)
                {
                    builder = CreateDbParametersBuilder();
                    builder.Create().Name("messageid").Type(DbType.String).Value(msg.MsgID);
                    builder.Create().Name("savedfilename").Type(DbType.String).Value(item.SavedFileName);
                    builder.Create().Name("uploadfilename").Type(DbType.String).Value(item.UploadFileName);
                    AdoTemplate.ExecuteNonQuery(CommandType.Text, insertAttachmentSql, builder.GetParameters());
                }
            }
            return true;
        }

        public IList<Msg> GetMsgSendList(string sendUserID)
        {
            string selectSql = @"select 
	                                Msg.messageid,messagesubject,senduserid,sendusername,hasattachment,sendtime,
                                    '' as sendto,'' as content,Cast(0 as bit) as hasread

                                from 
	                                Msg
                                inner join MessageRecvUser on  MessageRecvUser.messageid=Msg.messageid                               
                                where senduserid=@senduserid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("senduserid").Type(DbType.String).Value(sendUserID);
            return AdoTemplate.QueryWithRowMapper<Msg>(CommandType.Text, selectSql, new MsgRow<Msg>(), builder.GetParameters());
        }

        public IList<Msg> GetMsgRecvList(string recvuserID, int recordcount)
        {
            string selectSql = @"select 
	                                Msg.messageid,messagesubject,senduserid,sendusername,hasattachment,sendtime,
	                                '' as sendto,'' as content,hasread
                                from 
	                                MessageRecvUser
                                inner join  Msg on MessageRecvUser.messageid=Msg.messageid
                                where MessageRecvUser.recvuserid=@recvuserid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("recvuserid").Type(DbType.String).Value(recvuserID);
            return AdoTemplate.QueryWithRowMapper<Msg>(CommandType.Text, string.Format(selectSql, recordcount == 0 ? "" : ("top " + recordcount.ToString())), new MsgRow<Msg>(), builder.GetParameters());
        }

        internal class MsgRow<T> : IRowMapper<T> where T : Msg, new()
        {
            public T MapRow(IDataReader reader, int rowNum)
            {
                T msg = new T();
                msg.MsgID = reader.GetInt32(reader.GetOrdinal("messageid"));
                msg.Subject = reader.GetString(reader.GetOrdinal("messagesubject"));
                msg.SendUserID = reader.GetString(reader.GetOrdinal("senduserid"));
                msg.SendUserName = reader.GetString(reader.GetOrdinal("sendusername"));
                msg.HasAttachment = reader.GetBoolean(reader.GetOrdinal("hasattachment"));
                msg.SendTime = reader.GetDateTime(reader.GetOrdinal("sendtime"));
                msg.Content = reader.GetString(reader.GetOrdinal("content"));
                msg.RecvString = reader.GetString(reader.GetOrdinal("sendto"));
                msg.HasRead = reader.GetBoolean(reader.GetOrdinal("hasread"));
                return msg;
            }
        }

        public bool DeleteMsgSent(int msgID)
        {
            string deleteattachment = "delete from MessageAttachment where messageid=@messageid";
            string deletereadlist = "delete from MessageRecvUser where messageid=@messageid";
            string deletereply = "delete from MessageReply where messageid=@messageid";
            string deleteSql = @"delete from Msg where messageid=@messageid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("messageid").Type(DbType.Int32).Value(msgID);
            AdoTemplate.ExecuteNonQuery(CommandType.Text, deleteattachment, builder.GetParameters());
            AdoTemplate.ExecuteNonQuery(CommandType.Text, deletereply, builder.GetParameters());
            AdoTemplate.ExecuteNonQuery(CommandType.Text, deletereadlist, builder.GetParameters());
            AdoTemplate.ExecuteNonQuery(CommandType.Text, deleteSql, builder.GetParameters());
            return true;
        }

        public Msg GetMsgDetailByID(int msgID)
        {
            string selectSql = @"select 
	                                messageid,messagesubject,senduserid,sendusername,hasattachment,sendtime,Cast(0 as bit) as hasread,
	                                 sendto,content
                                from 
	                                Msg 
                                where messageid=@msgid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("msgid").Type(DbType.Int32).Value(msgID);
            try
            {
                return AdoTemplate.QueryForObject<Msg>(CommandType.Text, selectSql, new MsgRow<Msg>(), builder.GetParameters());
            }
            catch (Exception)
            {
                return null;
            }
        }

        public Attachment GetMsgAttachment(int msgid, int fileid)
        {
            string selectSql = @"select [autoid],messageid,savedfilename,uploadfilename
                                    from
                                    MessageAttachment
                                    where messageid=@messageid and [autoid]=@fileid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("messageid").Type(DbType.Int32).Value(msgid);
            builder.Create().Name("fileid").Type(DbType.Int32).Value(fileid);
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());
            if (dt.Rows.Count == 0) return null;
            Attachment att = new Attachment();
            att.FileID = fileid;
            att.RefParentID = msgid;
            att.SavedFileName = dt.Rows[0]["savedfilename"].ToString();
            att.UploadFileName = dt.Rows[0]["uploadfilename"].ToString();
            return att;
        }

        public IList<Attachment> GetMsgAttachments(int msgID)
        {
            string selectSql = @"select [autoid],savedfilename,uploadfilename
                                    from
                                    MessageAttachment
                                    where messageid=@messageid";


            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("messageid").Type(DbType.Int32).Value(msgID);
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());
            IList<Attachment> attachmentlist = new List<Attachment>();
            foreach (DataRow item in dt.Rows)
            {
                Attachment attachment = new Attachment();
                attachment.FileID = (int)item["autoid"];
                attachment.RefParentID = msgID;
                attachment.UploadFileName = item["uploadfilename"].ToString();
                attachment.SavedFileName = item["savedfilename"].ToString();
                attachmentlist.Add(attachment);
            }
            return attachmentlist;
        }

        public IList<RecvUser> GetMsgRecvUserList(int msgID)
        {
            string selectSql = @"select recvuserid,recvusername,hasread
                                from MessageRecvUser where messageid=@messageid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("messageid").Type(DbType.Int32).Value(msgID);
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());

            IList<RecvUser> rulist = new List<RecvUser>();
            foreach (DataRow item in dt.Rows)
            {
                RecvUser ru = new RecvUser();
                ru.UserID = item["recvuserid"].ToString();
                ru.UserName = item["recvusername"].ToString();
                ru.HaveRead = (bool)item["hasread"];
                rulist.Add(ru);
            }
            return rulist;
        }


        public IList<RecvUser> GetMsgReadList(int msgid)
        {
            string selectSql = @"select recvuserid,recvusername,readtime from MessageRecvUser
                                   where messageid=@messageid and hasread=1";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("messageid").Type(DbType.Int32).Value(msgid);
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());
            IList<RecvUser> userList = new List<RecvUser>();
            foreach (DataRow item in dt.Rows)
            {
                RecvUser user = new RecvUser();
                user.HaveRead = true;
                user.UserID = item["recvuserid"].ToString();
                user.UserName = item["recvusername"].ToString();
                user.ReadTime = (DateTime)item["readtime"];
                userList.Add(user);
            }
            return userList;
        }

        public IList<RecvUser> GetMsgUnReadList(int msgid)
        {
            string selectSql = @"select recvuserid,recvusername from MessageRecvUser
                                   where messageid=@messageid and hasread=0";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("messageid").Type(DbType.Int32).Value(msgid);
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());
            IList<RecvUser> userList = new List<RecvUser>();
            foreach (DataRow item in dt.Rows)
            {
                RecvUser user = new RecvUser();
                user.HaveRead = false;
                user.UserID = item["recvuserid"].ToString();
                user.UserName = item["recvusername"].ToString();
                userList.Add(user);
            }
            return userList;
        }


        public void UpdateMsgUserReadStatusToRead(int msgid, string userid)
        {
            string updateSql = @"update MessageRecvUser 
                                set hasread=1,readtime=@readtime 
                                where messageid=@messageid and recvuserid=@recvuserid and hasread=0";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("messageid").Type(DbType.Int32).Value(msgid);
            builder.Create().Name("recvuserid").Type(DbType.String).Value(userid);
            builder.Create().Name("readtime").Type(DbType.DateTime).Value(DateTime.Now);
            AdoTemplate.ExecuteNonQuery(CommandType.Text, updateSql, builder.GetParameters());
        }


        public void UpdateAfficheUserReadStatus(int afficheid, LoginUser user)
        {
            string opsql = @"if not exists(
	                        select 1 from AfficheReadList 
	                        where afficheid=@afficheid and readuserid=@readuserid)
	                        insert into AfficheReadList (afficheid,readuserid,recvusername,readtime) 
	                        values (@afficheid,@readuserid,@recvusername,@readtime)";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("afficheid").Type(DbType.Int32).Value(afficheid);
            builder.Create().Name("readuserid").Type(DbType.String).Value(user.UserID);
            builder.Create().Name("recvusername").Type(DbType.String).Value(user.UserName);
            builder.Create().Name("readtime").Type(DbType.DateTime).Value(DateTime.Now);
            AdoTemplate.ExecuteNonQuery(CommandType.Text, opsql, builder.GetParameters());
        }


        public void UpdateWorkInfoUserReadStatus(int workInfoid, LoginUser user)
        {
            string opsql = @"if not exists(
	                        select 1 from WorkReadList 
	                        where workid=@workid and readuserid=@readuserid)
	                        insert into WorkReadList (workid,readuserid,recvusername,readtime) 
	                        values (@workid,@readuserid,@recvusername,@readtime)";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("workid").Type(DbType.Int32).Value(workInfoid);
            builder.Create().Name("readuserid").Type(DbType.String).Value(user.UserID);
            builder.Create().Name("recvusername").Type(DbType.String).Value(user.UserName);
            builder.Create().Name("readtime").Type(DbType.DateTime).Value(DateTime.Now);
            AdoTemplate.ExecuteNonQuery(CommandType.Text, opsql, builder.GetParameters());
        }


        public void ReplyMsg(int msgid, LoginUser user, string content)
        {
            string insertSql = @"insert into MessageReply (messageid,replyuserid,replyusername,content,replytime) values(@messageid,@replyuserid,@replyusername,@content,@replytime)";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("messageid").Type(DbType.Int32).Value(msgid);
            builder.Create().Name("replyuserid").Type(DbType.String).Value(user.UserID);
            builder.Create().Name("replyusername").Type(DbType.String).Value(user.UserName);
            builder.Create().Name("content").Type(DbType.String).Value(content);
            builder.Create().Name("replytime").Type(DbType.DateTime).Value(DateTime.Now);
            AdoTemplate.ExecuteNonQuery(CommandType.Text, insertSql, builder.GetParameters());
        }


        public IList<MsgReplyInfo> GetMsgReplyInfoList(int msgid)
        {
            string selectSql = @"select [autoid],messageid,replyuserid,replyusername,content,replytime from MessageReply
                                    where messageid=@messageid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("messageid").Type(DbType.Int32).Value(msgid);
            return AdoTemplate.QueryWithRowMapper<MsgReplyInfo>(CommandType.Text, selectSql, new MsgReplyRow<MsgReplyInfo>(), builder.GetParameters());
        }

        internal class MsgReplyRow<T> : IRowMapper<T> where T : MsgReplyInfo, new()
        {
            public T MapRow(IDataReader reader, int rowNum)
            {
                T msgreply = new T();
                msgreply.Content = reader.GetString(reader.GetOrdinal("content"));
                msgreply.MsgID = reader.GetInt32(reader.GetOrdinal("messageid"));
                msgreply.ReplyID = reader.GetInt32(reader.GetOrdinal("autoid"));
                msgreply.ReplyTime = reader.GetDateTime(reader.GetOrdinal("replytime"));
                msgreply.ReplyUserID = reader.GetString(reader.GetOrdinal("replyuserid"));
                msgreply.ReplyUserName = reader.GetString(reader.GetOrdinal("replyusername"));
                return msgreply;
            }
        }


        public int GetAfficheCountInGrade(LoginUser user)
        {
            string selectSql = "select count(*) from AfficheInfo where collegeid=@collegeid and gradeid=@gradeid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("collegeid").Type(DbType.Int32).Value(user.CollegeID);
            builder.Create().Name("gradeid").Type(DbType.Int32).Value(user.GradeID);
            return (int)AdoTemplate.ExecuteScalar(CommandType.Text, selectSql, builder.GetParameters());
        }


        public int GetMailUnreadCount(LoginUser user)
        {
            string selectSql = "select count(*) from MailRecvUser where recvuserid=@recvuserid and hasread=0";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("recvuserid").Type(DbType.String).Value(user.UserID);           
            return (int)AdoTemplate.ExecuteScalar(CommandType.Text, selectSql, builder.GetParameters());
        }


        public void UpdateMailRead(int maiID, string userid)
        {
            string updateSql = "update MailRecvUser set hasread=1 where mailid=@mailid and recvuserid=@recvuserid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("mailid").Type(DbType.Int32).Value(maiID);
            builder.Create().Name("recvuserid").Type(DbType.String).Value(userid);
            AdoTemplate.ExecuteNonQuery(CommandType.Text, updateSql, builder.GetParameters());
        }
    }

}

